﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace DeutscheRemoval.Utils
{
    public class SQLUtil
    {
        private SqlConnection _conn = null;
        public SqlConnection Connection
        {
            get { return _conn; }
        }
        public SQLUtil()
        {
        }
        public string InitConnection(string urlServer, string userDB, string passDB, string nameDb)
        {
            string output = "OK";
            try
            {
                string connectionString = "user id=" + userDB + ";" +
                                           "password=" + passDB + ";server=" + urlServer + ";" +
                                           "Trusted_Connection=no;" +
                                           "database=" + nameDb + "; " +
                                           "connection timeout=30";
                _conn = new SqlConnection();
                _conn.ConnectionString = connectionString;
                _conn.Open();
            }
            catch (SqlException sqlex)
            {
                output = sqlex.ToString();
            }
            catch (Exception ex) 
            {
                output = ex.ToString();
            }
            return output;
        }
        public string CloseConnection()
        {
            string output = "OK";
            try
            {
                _conn.Close();
            }
            catch (SqlException sqlex)
            {
                output = sqlex.ToString();
            }
            catch (Exception ex)
            {
                output = ex.ToString();
            }
            return output;
        }
        public DataTable ExtractValues(string query)
        {
            DataTable table = new DataTable();
            try
            {
                SqlDataAdapter dataAdapter = new SqlDataAdapter(query, Connection);
                SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
                table.Locale = System.Globalization.CultureInfo.InvariantCulture;
                dataAdapter.Fill(table);
            }
            catch (SqlException sqlex)
            {
                Agos.Utils.Logger.Log(sqlex.ToString(), Agos.Utils.LogType.Error);
            }
            catch (Exception ex)
            {
                Agos.Utils.Logger.Log(ex.ToString(), Agos.Utils.LogType.Error);
            }
            return table;
        }
    }
}
